import os.path

import pandas as pd
import numpy as np
from datetime import datetime
import warnings
import openpyxl
from openpyxl import load_workbook
import re
import json
import requests

import xlrd
import xlwt


# 读取OPO数据写入模板表中
def readOPO():
    Json数据 = [{'SalesOrgNo': '6090', 'CustomerCode': 'Q3832', 'CustomerName': '富士康', 'PONum': '', 'DataType': '1',
                 'RequestDate': '', 'FileType': 'Excel', 'Creator': 'RPA', 'Status': '1', 'Standby': '',
                 'PreProcessTime': '', 'Lines': []}]
    headMap = {'SalesOrgNo': 1, 'DataType': 2, 'PreProcessTime': 3, 'Creator': 4, 'FileType': 5, 'Status': 6,
               'CustomerCode': 7, 'CustomerName': 8}
    path = 'D:\zm\越南裕华客服跟单模块流程盘点 (第二版)\文件\新建文件夹\Q3832的OPO.xls'
    readOPOdate = pd.read_excel(path, dtype=str)
    print(readOPOdate.columns.tolist())
    # headList=['PO 编号', '订单日期', '项目','项目说明','需要日期', '单位', '已订购',  '价格', 'OPO数量','组织编码']
    headList = ['Plant', 'PO', 'PN', 'DeliveryDate', 'Original_Qty', 'Total_OpenQty', 'ReleaseDate', 'Description']
    readOPOdate = readOPOdate[headList]
    print(readOPOdate)
    LineMap = {'Plant': 9, 'PO': 10, 'PN': 13, 'Description': 14, 'Original_Qty': 18, 'Total_OpenQty': 19,
               'ReleaseDate': 22, 'DeliveryDate': 23}
    给数模板 = 'D:\zm\越南裕华客服跟单模块流程盘点 (第二版)\文件\新建文件夹\给数据模板表.xlsx'
    wb = openpyxl.load_workbook(给数模板)
    sheet = wb['Q3832的OPO']
    start_row = 4
    for index, items in readOPOdate.iterrows():
        for headkey, headval in headMap.items():
            sheet.cell(row=start_row, column=headval).value = Json数据[0][headkey]

        for Linekey, Lineval in LineMap.items():
            # print(key)
            # print(items)
            sheet.cell(row=start_row, column=Lineval).value = items[Linekey]
        start_row += 1

    wb.save(给数模板)


# readOPO()
# 读取排期表数据然后写入到模板表中
#
# dat=pd.read_excel(文件)
# print(dat['物料'])

def readdate():
    pd.set_option('display.max_columns', None)

    排期文件 = 'D:\zm\越南裕华客服跟单模块流程盘点 (第二版)\文件\新建文件夹\\N115排期.xlsx'
    readPlanDate = pd.read_excel(排期文件, dtype=str, sheet_name='Sheet1')
    PlanDate1 = readPlanDate[['物料', '物料描述', 'OPO数量']]
    print(readPlanDate.columns.values.tolist())
    col_List = []
    for col_name in readPlanDate.columns.tolist():
        # print(str(col_name))
        try:  # 尝试将列名转换为 datetime 对象
            # print(times)
            col_List.append(str(col_name).split(' ')[0])
            # 转换为字符串p
        except ValueError:  # 如果转换失败，则直接返回原列名
            col_List.append(col_name)  # 更新列名为字符串格式
    print(col_List)
    readPlanDate.columns = col_List
    print(readPlanDate.columns.tolist())
    # 假设表头的日期字段名为'date_field'
    # date_field = df['date_field']

    # 将datetime对象转换为字符串格式的日期
    # formatted_dates = date_field.dt.date

    # 获取要货计划表中的客户料号和对应时间数据,获取对应的SAP料号

    # print(readPlanDate.dtypes)
    # # 利用正则表达式获取到表头为时间类型的列
    date_pattern = r'\d{4}-\d{2}-\d{2}'
    # date_pattern = datetime.datetime(2024, 7, 4, 0, 0)
    column_names = readPlanDate.columns[
        readPlanDate.columns.str.contains(date_pattern, case=False, na=False, regex=True)]
    timecolumn = column_names.values.tolist()
    print(timecolumn)

    # pattern = r'\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}'
    # regex_datetime_columns = []
    # for col in readPlanDate.columns:
    #     if readPlanDate[col].dtype == 'object':  # 假设日期时间以字符串形式存储
    #         if any(re.match(pattern, str(val)) for val in readPlanDate[col].dropna()):
    #             regex_datetime_columns.append(col)
    # print(regex_datetime_columns)
    # 将两个表数据进行合并
    PlanDate2 = readPlanDate[timecolumn]
    PlanDate2.fillna(0, inplace=True)
    for column in timecolumn:
        PlanDate2[column].astype(float)
    concatDate = pd.concat([PlanDate1, PlanDate2], axis=1)
    print(concatDate)
    给数模板 = 'D:\zm\越南裕华客服跟单模块流程盘点 (第二版)\文件\新建文件夹\给数据模板表.xlsx'
    wb = openpyxl.load_workbook(给数模板)
    sheet = wb['N115排期']
    start_row = 4
    list = [13, 20, 21, 24]
    cols = concatDate.columns.tolist()
    # print(cols)
    for items in concatDate.values.tolist():
        料号 = items[0]
        描述 = items[1]
        OPO = items[2]
        Index = 0
        times = 0
        # print(items)
        # print(len(cols))
        # print(len(items))
        for item in items:
            if times >= 3:
                # print(times)
                日期 = cols[times]
                # print(日期)
                sheet.cell(row=start_row, column=13).value = 料号
                sheet.cell(row=start_row, column=14).value = 描述
                sheet.cell(row=start_row, column=19).value = OPO
                sheet.cell(row=start_row, column=20).value = item
                sheet.cell(row=start_row, column=23).value = 日期
                start_row += 1
            times += 1

    wb.save(给数模板)


# readdate()

import requests


# 调用接口将数据写入数据库中
def getIdentityToken():
    url = 'http://10.0.23.11:8080/yuto/rest/core/auth/login'
    # 要发送的数据
    body = {
        "userName": "RPA_System",
        "password": "RPASystem&*^852"
    }
    # 发送POST请求
    response = requests.post(url, json=body)
    # 检查响应状态码
    if response.status_code == 200:
        # 打印响应内容
        ret = response.text
        jsondate = json.loads(ret)
        print(jsondate['identitytoken'])
        # return jsondate['identitytoken']
    else:
        print(f"请求失败，状态码：{response.status_code}")


# re=getIdentityToken()
# print(re)
# time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
# 预处理时间=str(time)
预处理时间 = str("2024-10-16 15:44:38")
print(预处理时间)


# JsonDate=[{'SalesOrgNo': '6090', 'CustomerCode': 'Q3832', 'CustomerName': '富士康',  'PONum': '', 'DataType': '1', 'RequestDate': '', 'FileType': 'Excel', 'Creator': 'RPA', 'Status': '1', 'Standby': '', 'PreProcessTime': 预处理时间, 'Lines': []}]
def toPOST(JsonDate):
    # print("调用接口{}".format(JsonDate))
    # 目标URL
    url = 'http://10.0.23.11:8080/yuto/bg/SOS.OMS/TADBll/ImportPO'
    # 要发送的数据
    # re = getIdentityToken()
    header = {
        'identitytoken': 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVDEifQ.eyJleHAiOjE3Mjk0OTg3NTAsInVzZXJJZCI6IlJQQV9TeXN0ZW0iLCJwcm9wcyI6IiJ9.RwH-agSdaaDVn2mN5GXahKmeLtc0ESJdYWf_AsGHMYA'
    }
    # 发送POST请求
    response = requests.post(url, headers=header, json=JsonDate)
    # 检查响应状态码
    if response.status_code == 200:
        # 打印响应内容
        print(response.text)
    else:
        print(f"请求失败，状态码：{response.status_code}")


# toPOST(JsonDate)

# 读取模板表中的数据，并整理成规范的JSON数据，然后调用接口
def toJSON():
    给数模板 = 'D:\zm\越南裕华客服跟单模块流程盘点 (第二版)\文件\新建文件夹\给数据模板表.xlsx'
    read = pd.read_excel(给数模板, dtype=str, sheet_name='Q3832的OPO', header=1)
    # read['PricePerQty'].fillna(1,inplace=True)
    # read['DeliveryQty'].fillna(0,inplace=True)
    read.fillna('', inplace=True)
    # print(read['PricePerQty'])
    read = read[~read['SalesOrgNo'].isin(['销售组织'])].reset_index(drop=True)
    total = len(read)
    # 将一些列转换为数字类型的
    colList = ['PoQuantity', 'PoOpenQty', 'DeliveryQty', 'PricePerQty']
    # for col in colList:
    #     print(col)
    #     read[col] = read[col].astype(float)

    # print(read)
    # groups = read.groupby('PONum')#PO时groupBy  （PONum）
    # groups = read.groupby('CustItemNum')#排期时groupBy（CustItemNum）
    groups = read.groupby(read.index // 100)  # OPO数据通过每100行进行拆分
    length = 0
    for name, group in groups:
        dateList = []
        if name in ['PONum', 'PO号', '客户料号', 'CustItemNum']:
            continue
        groupDick = group.to_dict('records')
        # print(groupDick)
        headList = ["SalesOrgNo", "CustomerCode", "CustomerName", "DataType", "RequestDate",
                    "FileType", "Creator", "Status", "Standby"]
        linesList = ["CustOrgNo", "PONum", "POLineNum", "Currency", "Unit", "PricePerQty", "Price", "CustItemNum",
                     "CustItemName",
                     "PoQuantity", "PoOpenQty", "DeliveryDate", "DeliveryQty", "RequestDate"]
        datemap = {}
        linelist = []

        for item in groupDick:
            # print(item)
            for key in headList:
                datemap[key] = item[key]
            # time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            datemap['PreProcessTime'] = 预处理时间  # 预处理时间必须与第一次预处理的时间相同
            datemap['PONum'] = ''  # PONum必须与第一次预处理的相同
            lineMap = {}
            for line in linesList:
                lineMap[line] = item[line]
            linelist.append(lineMap)
        datemap['Lines'] = linelist
        datemap['RequestDate'] = ''
        # 一些其他的头字段的写入 批号：BatchSeq 总行数：TotalCount 单批调用的行数：LineCount
        length += 1
        datemap['BatchSeq'] = length  # 当前是第几批调用接口
        datemap['TotalCount'] = total  # 当前文件数据总行数
        datemap['LineCount'] = len(linelist)  # 汇总当前行的数量
        dateList.append(datemap)
        print("第{}次调用接口,line长度{}:数据为 {}".format(length, len(linelist), dateList))

        toPOST(dateList)
        das = str(dateList)
        valid_json_str = das.replace("'", '"')
        # print(valid_json_str)
        txt = 'Json数据.txt'
        # 使用open()
        # 函数打开文件，如果文件不存在则创建它
        with open(txt, "a", encoding='utf-8') as file:
            # 使用write()方法将字符串写入文件
            file.write("\n\n")
            file.write(valid_json_str)


toJSON()

# len =['1','2']
# [inde +',' for inde in len]

# 确认是否有系统（能否实现），确认是否有多个模板，
#
# import pandas as pd
#
# # 创建一个简单的DataFrame
# df = pd.DataFrame({'A': [1, 2, 3], 'B': [4, 5, 6]})
#
# # 使用iterrows遍历DataFrame
# for index, row in df.iterrows():
#     print(f"Index: {index}, Row: {row}")
#     print('数据{}'.format(row['A']))
